/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.nxlv2.nomad.accessdb.dao.jdbc;

import com.nxlv2.nomad.accessdb.dao.DAOException;
import com.nxlv2.nomad.accessdb.dao.IPrixDao;
import com.nxlv2.nomad.accessdb.dao.IncoherentResultException;
import com.nxlv2.nomad.accessdb.dao.NoResultException;
import com.nxlv2.nomad.businessobjects.DetailCommande;
import com.nxlv2.nomad.businessobjects.Prix;
import com.nxlv2.nomad.businessobjects.Tva;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 *
 * @author Nastasia
 */
public class JdbcPrixDao implements IPrixDao {

    private static final Logger logger = LoggerFactory.getLogger(JdbcPrixDao.class);
    private static final ConnectionManager manager = new ConnectionManager();

    /**
     *
     * @param prix
     * @throws DAOException
     */
    @Override
    public void savePrix(Prix prix) throws DAOException {
        if (null == prix) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        Connection con = manager.creerConnection();

        StringBuilder builder = new StringBuilder();
        builder.append("INSERT INTO prix (idBase, prixUht, date, dateCreation) ");
        builder.append("VALUES (?, ?, ?, NOW())");

        String request = builder.toString();
        logger.debug("Insert prix: {}", request);

        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request, PreparedStatement.RETURN_GENERATED_KEYS);
        } catch (SQLException e) {
            logger.error("Can not prepare statement", e);
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, prix.getIdBase());
            ps.setDouble(2, prix.getPrixUht());
            ps.setDate(3, new java.sql.Date(prix.getDate().getTime()));
            ps.executeUpdate();
        } catch (SQLException e) {
            logger.error("Can not populate request with data.");
            throw new DAOException("Can not populate request with data.", e);
        }

        try {
            ResultSet rs = ps.getGeneratedKeys();

            int pk = 0;

            while (rs.next()) {
                pk = rs.getInt(1);
            }
            prix.setIdAppli(pk);

        } catch (SQLException e) {
            logger.error("Can not execute insert in db.");
            throw new DAOException("Can not execute insert in db.", e);
        }
        manager.fermerConnection();
    }

    /**
     *
     * @param id
     * @throws DAOException
     */
    @Override
    public void deletePrix(int id) throws DAOException {
        if (id < 1) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        Connection con = manager.creerConnection();

        StringBuilder builder = new StringBuilder();
        builder.append("DELETE FROM prix WHERE idAppli = ?");
        String request = builder.toString();
        logger.debug("Delete prix: {}", request);

        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, id);
            ps.executeUpdate();
        } catch (SQLException e) {
            logger.error("Can not delete prix with id.");
            throw new DAOException("Can not delete prix with id.", e);
        }
        manager.fermerConnection();
    }

    /**
     *
     * @param prix
     * @throws DAOException
     */
    @Override
    public void updatePrix(Prix prix) throws DAOException {
        if (null == prix) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        Connection con = manager.creerConnection();

        StringBuilder builder = new StringBuilder();

        builder.append("UPDATE prix SET ");
        builder.append("idBase = ?, ");
        builder.append("prixUht = ?, ");
        builder.append("date = ?, ");
        builder.append("dateModification = NOW() ");
        builder.append("WHERE idAppli = ?");

        String request = builder.toString();
        logger.debug("Update prix: {}", request);

        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, prix.getIdBase());
            ps.setDouble(2, prix.getPrixUht());
            ps.setDate(3, new java.sql.Date(prix.getDate().getTime()));
            ps.setInt(4, prix.getIdAppli());
            ps.executeUpdate();
        } catch (SQLException e) {
            logger.error("Can not populate request with data.");
            throw new DAOException("Can not populate request with data.", e);
        }
        manager.fermerConnection();
    }

    /**
     *
     * @param id
     * @return Prix
     * @throws DAOException
     */
    @Override
    public Prix findPrixById(int id) throws DAOException {
        if (id < 1) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        StringBuilder builder = new StringBuilder();
        builder.append("SELECT idAppli, idBase, prixUht, date ");
        builder.append("FROM prix WHERE idAppli = ?");

        String request = builder.toString();
        logger.debug("get prix: {}", request);
        logger.debug("id: {}", id);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, id);
            ps.executeQuery();
        } catch (SQLException e) {
            logger.error("Can not select data with id.");
            throw new DAOException("Can not select data with id.", e);
        }
        ResultSet rst;
        try {
            rst = ps.getResultSet();
        } catch (SQLException e) {
            logger.error("Can not get resultset.");
            throw new DAOException("Can not get resultset.", e);
        }

        List<Prix> listPrix = null;
        try {
            listPrix = map(rst);
        } catch (SQLException e) {
            logger.error("Can not populate map with result.");
            throw new DAOException("Can not populate map with result.", e);
        }

        if (null == listPrix || listPrix.isEmpty()) {
            throw new NoResultException("No adresse found with id " + id);
        }
        if (listPrix.size() > 1) {
            throw new IncoherentResultException("Many records with a same id.");
        }
        manager.fermerConnection();
        return listPrix.get(0);
    }

    /**
     *
     * @param rst
     * @return List<Prix>
     * @throws SQLException
     */
    private List<Prix> map(ResultSet rst) throws SQLException {
        List<Prix> listPrix = new ArrayList<>();

        while (rst.next()) {
            Prix prix = new Prix();
            prix.setDate(rst.getDate("date"));
            prix.setIdAppli(rst.getInt("idAppli"));
            prix.setIdBase(rst.getInt("idBase"));
            prix.setPrixUht(rst.getDouble("prixUht"));
            listPrix.add(prix);
        }

        return listPrix;

    }

    /**
     *
     * @param prix
     * @return Tva
     * @throws DAOException
     */
    @Override
    public Tva getTva(Prix prix) throws DAOException {
        if (null == prix) {
            throw new IllegalArgumentException("Can not save a null object.");
        }
        StringBuilder builder = new StringBuilder();
        builder.append("SELECT tva.idBase, tva.idAppli, ");
        builder.append("tva.taux, AES_DECRYPT(tva.libelle,?) AS libelle ");
        builder.append("FROM prix ");
        builder.append("JOIN tva ON tva.idAppli = prix.idAppliTva ");
        builder.append("WHERE prix.idAppli = ?");

        String request = builder.toString();
        logger.debug("Select tva par prix: {}", request);
        logger.debug("prix {}", prix);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setString(1, manager.getKeyAES());
            ps.setInt(2, prix.getIdAppli());
            ps.executeQuery();

        } catch (SQLException e) {
            logger.error("Can not select data with id.");
            throw new DAOException("Can not select data with id.", e);
        }

        ResultSet rst;
        try {
            rst = ps.getResultSet();
        } catch (SQLException e) {
            logger.error("Can not get resultset.");
            throw new DAOException("Can not get resultset.", e);
        }

        List<Tva> tvas = null;
        try {
            tvas = mapTva(rst);
        } catch (SQLException e) {
            logger.error("Can not populate map with result.", e);
            throw new DAOException("Can not populate map with result.", e);
        }

        if (null == tvas || tvas.isEmpty()) {
            throw new NoResultException("No tva found with prix : " + prix);
        }
        manager.fermerConnection();
        return tvas.get(0);
    }

    /**
     *
     * @param tva
     * @param prix
     * @throws DAOException
     */
    @Override
    public void lierTvaAPrix(Tva tva, Prix prix) throws DAOException {
        if (null == tva || null == prix) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        StringBuilder builder = new StringBuilder();

        builder.append("UPDATE prix ");
        builder.append("SET idAppliTva = ?, ");
        builder.append("dateModification = NOW() ");
        builder.append("WHERE idAppli = ?");

        String request = builder.toString();
        logger.debug("Lier tva à prix : {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, tva.getIdAppli());
            ps.setInt(2, prix.getIdAppli());
            ps.executeUpdate();
        } catch (SQLException e) {
            logger.error("Can not link tva to prix.");
            throw new DAOException("Can not link tva to prix.", e);
        }
        manager.fermerConnection();
    }

    /**
     *
     * @param rst
     * @return List<Tva>
     * @throws SQLException
     */
    private List<Tva> mapTva(ResultSet rst) throws SQLException {
        List<Tva> tvas = new ArrayList<>();

        while (rst.next()) {
            Tva tva = new Tva();
            tva.setIdAppli(rst.getInt("idAppli"));
            tva.setIdBase(rst.getInt("idBase"));
            tva.setLibelle(rst.getString("libelle"));
            tva.setTaux(rst.getDouble("taux"));
            tvas.add(tva);
        }
        return tvas;
    }
}
